package persistence;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import persistence.exception.DAOException;
import persistence.util.JdbcUtil;

public class CostUnitDAOJdbcImpl extends JdbcDAOBase implements CostUnitDAO{
	
	private LibraryLocationDAO libraryLocationDAO;
	
	public CostUnitDAOJdbcImpl() {
		super();
	}

	public LibraryLocationDAO getLibraryLocationDAO() {
		return libraryLocationDAO;
	}
	public void setLibraryLocationDAO(LibraryLocationDAO libraryLocationDAO) {
		this.libraryLocationDAO = libraryLocationDAO;
	}


	@Override
	public CostUnit find(int id) throws DAOException {
		String sql = "SELECT id, name, nummer, bibliotheksstandorte_id FROM kostenstellen" +
					 " WHERE id = ?";
		Connection con = null;
		PreparedStatement prepStmt = null;
		ResultSet rs = null;
		CostUnit costUnit = null;
		LibraryLocation libraryLocation = null;
		
		try {
			con = getConProvider().getConnection();
			prepStmt = con.prepareStatement(sql);
			prepStmt.setInt(1, id);
			rs = prepStmt.executeQuery();
			
			if (rs.next()) {
				libraryLocation = libraryLocationDAO.find(rs.getInt(4));
				costUnit = new CostUnit (rs.getInt(1), rs.getString(2), rs.getInt(3), libraryLocation);
			}
			
		}
		catch (SQLException se) {
			throw new DAOException("Problem when attempting to find CostUnit with id: " + id, se);
		}
		finally {
			JdbcUtil.cleanup(con, prepStmt, rs);
		}
		return costUnit;
	}

	@Override
	public List<CostUnit> findAllByTitle(String title) throws DAOException{
		String sql = "SELECT id, name, nummer, bibliotheksstandorte_id FROM kostenstellen" +
					 " WHERE name LIKE ?";
		List<CostUnit> costUnits = new ArrayList<CostUnit>();
		CostUnit costUnit = null;
		LibraryLocation location = null;
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, title + "%");
			
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				location = libraryLocationDAO.find(rs.getInt(4));
				costUnit = new CostUnit(rs.getInt(1), rs.getString(2), rs.getInt(3), location);
				costUnits.add(costUnit);
			}			
		}
		catch (SQLException se) {
			throw new DAOException("Error when attempting to find all cost units", se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
		}
		return costUnits;
	}	


	@Override
	public List<CostUnit> findAll() throws DAOException {
		return findAllByTitle("");
	}

	@Override
	public List<CostUnit> findAllForLocation(LibraryLocation location) throws DAOException {
		
		String sql = "SELECT id, name, nummer, bibliotheksstandorte_id FROM kostenstellen WHERE bibliotheksstandorte_id = ?";
		List<CostUnit> costUnitsForLocation = new ArrayList<CostUnit>();
		CostUnit costUnit = null;
		
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		
		try {
			con = getConProvider().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setInt(1, location.getId());
			rs = stmt.executeQuery();
			
			while (rs.next()) {
				costUnit = new CostUnit(rs.getInt(1), rs.getString(2), rs.getInt(3), location);
				costUnitsForLocation.add(costUnit);
			}			
		}
		catch (SQLException se) {
			throw new DAOException("Error when attempting to find all cost units for LibraryLocation with id: " + location.getId(), se);
		}
		finally {
			JdbcUtil.cleanup(con, stmt, rs);
		}
		return costUnitsForLocation;
	}

	@Override
	public void updateCostUnit(CostUnit costUnit) throws DAOException{
		// TODO Auto-generated method stub
		
	}

	@Override
	public CostUnit create() throws DAOException{
		// TODO Auto-generated method stub
		return null;
	}

}
